/************************************************************
 * $Id : ast_db.cpp   2011/08/01
 *
 *       swhors@naver.com
 ************************************************************/

#include "config.h"

#include <iostream>
#include <stdio.h>
#include <pthread.h>

/* for odbc connection */
#ifdef HAVE_SQL_H
#include <sqltypes.h>
#include <sqlext.h>
#include <sql.h>
#endif /* HAVE_SQL_H */

#include <time.h>
#include <string.h>
#include <stdlib.h>

using namespace std;

#include "dbconstant.h"
#include "ast_util.h"
#include "ast_db.h"

/*
 * see retrieving ODBC Diagnotics
 * for a definition of extract_error().
 */
#ifdef HAVE_SQL_H
void extract_error( char        * aFunctionName,
                    SQLHANDLE     aHandle,
                    SQLSMALLINT   aType )
{
    SQLINTEGER    sCnt       = 0;
    SQLINTEGER    sNative    = 0;
    SQLCHAR        sState[7]  = {0,};
    SQLCHAR        sText[256] = {0,};
    SQLSMALLINT    sLen       = 0;
    SQLRETURN    sRet       = SQL_SUCCESS;

    fprintf( stderr,
             "\n"
             "The driver reported the following diagnostics whilst running "
             "%s\n\n",
             aFunctionName );

    do
    {
        sRet = SQLGetDiagRec( aType,
                              aHandle,
                              ++sCnt,
                              sState,
                              &sNative,
                              sText,
                              sizeof(sText),
                              &sLen );
        if( SQL_SUCCEEDED( sRet ) )
        {
            printf( "%s:%d:%d:%s\n", sState, sCnt, sNative, sText );
        }
        else
        {
           /* do nothing */
        }
    }
    while( sRet == SQL_SUCCESS  );
}

#endif /* HAVE_SQL_H */

#ifdef HAVE_SQL_H
SQLHENV     gEnv;
SQLHDBC     gDbc;
SQLHSTMT    gStmt;
//SQLRETURN   gRet; /* ODBC API sReturn status */
//SQLSMALLINT gColumns; /* number of columns in result-set */
#endif /* HAVE_SQL_H */

int online_sql_disconnect()
{
#ifdef HAVE_SQL_H
   /* disconnect */
   SQLDisconnect( gDbc );
   SQLFreeHandle( SQL_HANDLE_DBC, gDbc );
   SQLFreeHandle( SQL_HANDLE_ENV, gEnv );
   return 1;
#else /* HAVE_SQL_H */
   return 0;
#endif /* HAVE_SQL_H */
}

int online_sql_connect()
{
#ifdef HAVE_SQL_H
   SQLRETURN sSqlRet;
   SQLCHAR   sDBName[256];
   char      sErrTitle[64];
   char      sDsnName[128];
                  
   /* Allocate an environment handle */
   sSqlRet = SQLAllocHandle( SQL_HANDLE_ENV,
                             SQL_NULL_HANDLE,
                             &gEnv  );
   if( sSqlRet == SQL_SUCCESS )
   {
      /* We want ODBC 3 support */
      sSqlRet = SQLSetEnvAttr( gEnv,
                               SQL_ATTR_ODBC_VERSION,
                               ( void *)SQL_OV_ODBC3,
                               0  );
      if( sSqlRet == SQL_SUCCESS )
      {
         /* Allocate a connection handle */
         sSqlRet = SQLAllocHandle( SQL_HANDLE_DBC, gEnv, &gDbc  );
         if( sSqlRet == SQL_SUCCESS )
         {
            /* Connect to the DSN MyAsDB*/
            sprintf( sDsnName, "%s","DSN=MyAsDB;UID=asterisk;Pwd=asterisk"  ); 
            sSqlRet = SQLDriverConnect( gDbc,
                                        NULL,
                                        ( SQLCHAR*)&sDsnName,
                                        SQL_NTS,
                                        NULL,
                                        0,
                                        NULL,
                                        SQL_DRIVER_COMPLETE  );
            if( SQL_SUCCEEDED( sSqlRet ) )
            {
               if( sSqlRet == SQL_SUCCESS_WITH_INFO )
               {
                  snprintf( sErrTitle,
                            64,
                            "%s",
                            "SQLDriverConnect"  );
                  
                  extract_error( sErrTitle, gDbc, SQL_HANDLE_DBC  );
                  SQLDisconnect( gDbc  );
                  SQLFreeHandle( SQL_HANDLE_DBC, gDbc  );
                  return 0;
               }
               else
               {
                  sSqlRet = SQLGetInfo( gDbc,
                                        SQL_DBMS_NAME,
                                        ( SQLPOINTER )sDBName,
                                        sizeof( sDBName ),
                                        NULL );
                  if( sSqlRet == SQL_SUCCESS )
                  {
                     return 1;
                  }
                  else
                  {
                     SQLDisconnect( gDbc );
                     SQLFreeHandle( SQL_HANDLE_DBC,gDbc );
                  }
               }
               return 0;
            }
         }
      }
   }
#endif /* HAVE_SQL_H */
   return 0;
}

// set last talk time to counsellor by conference name.
// in     : aConfName( in )     - Conference Name.
// return : success - 1, fail - 0
int online_sql_set_onconf( char  *aConfName, bool bOnConf )
{

#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   SQLCHAR   sQuery[256];
   char      sTVal[128];
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT,
                             gDbc,
                             &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      ( void )current_time_to_str( sTVal,128 );
      
      snprintf( ( char*)&sQuery,
                256,
                "update online set onconf='%d' "\
                "where member_id='0' and conference='%s';",
                ( bOnConf==true?1:0 ),
                aConfName );
      
      sSqlRet = SQLPrepare( gStmt, sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
            return 1;
         }
         else
         {
             /* nothing done */
         }
      }
      else
      {
         /* do nothing */
      }
      SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
   }
#endif /* HAVE_SQL_H */
   return 0;
}

/************************************************************
 * set last talk time to counsellor by conference name.
 *
 * @param [in] - aConfName( in )     - Conference Name.
 *
 * @return : success - 1, fail - 0
 ************************************************************/
int online_sql_set_lasttalk( char *aConfName,
                             char *aOutChanName )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[256];
   char      sTVal[128];
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      ( void )current_time_to_str( sTVal, 128  );
      snprintf( sQuery,
                256,
                UPD_ONLINE_WHERE1,
                sTVal,
                aConfName,
                aOutChanName );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            SQLFreeHandle( SQL_HANDLE_STMT, gStmt  );
            return 1;
         }
         else
         {
             /* nothing done */
         }
      }
      else
      {
         /* do nothing */
      }
      SQLFreeHandle( SQL_HANDLE_STMT, gStmt  );
   }
#endif /* HAVE_SQL_H */
   return 0;
}

// get counsellor by conference name.
// in     : aConfName( in )     - Conference Name.
//        : aChannel( out )     - Channel Buffer.
//        : aChannel( in )      - length of Channel Buffer.
// return : success - 1, fail - 0
int online_sql_get_counsellor( char  *aConfName, char *aChannel, int aChLen )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[256];
   struct tm sTimeVal;
   char       result[128];
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                256,
                SEL_CHA_ONLINE_WHERE4 );
      
      ( void )current_time(&sTimeVal );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         if( SQLExecute( gStmt ) == SQL_SUCCESS )
         {
            sSqlRet = SQLBindCol( gStmt,
                                  1,
                                  SQL_C_CHAR,
                                  result,
                                  128,
                                  NULL  );
            if( sSqlRet == SQL_SUCCESS )
            {
               sSqlRet = SQLFetch( gStmt );
               if( sSqlRet == SQL_SUCCESS )
               {
                  snprintf( aChannel, aChLen, "%s", result );
                  SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
                  return 1;
               }
            }
         }
      }
      SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
   }
#endif /* HAVE_SQL_H */
   return 0;
}

// get enable counsellor list by readytimeval.
// in     : aOnconf( in )       - counsellor is on-conference.
//        : aReadyTimeVal( in ) - Reay time value.
//        : channel( out )      - channel list buffer
//        : aCount( in/out )   - count of counsellor.
// return : success - 1, fail - 0
// 
int online_sql_get_counsellorlist( int   aOnconf,
                                   int   aReadyTimeVal,
                                   char  aChannel[][64],
                                   int   *aCount )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   int       sCount=0;
   
   char      sCounsellorName[64];
   char      sTimeStr[24];
   char      sQuery[256];
   struct tm sTimeVal;
   time_t    sT1,sT2;
   
   *aCount = 0;
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                256,
                SEL_CONF_ONLINE_WHERE1,
                aOnconf );
      
      (void)current_time(&sTimeVal );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet== SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet  == SQL_SUCCESS )
         {
            int sState = 0;
            sSqlRet = SQLBindCol( gStmt,
                                  1,
                                  SQL_C_CHAR,
                                  &sCounsellorName,
                                  64,
                                  NULL  );
            if( sSqlRet != SQL_SUCCESS )
            {
                goto close_sql;
            }
            
            sSqlRet = SQLBindCol( gStmt,
                                  2,
                                  SQL_C_CHAR,
                                  &sTimeStr,
                                  24,
                                  NULL  );
            
            if( sSqlRet != SQL_SUCCESS )
            {
                goto close_sql;
            }
            
            sSqlRet = SQLBindCol( gStmt,
                                  3,
                                  SQL_C_SSHORT,
                                  &sState,
                                  24,
                                  NULL  );
            
            while( sSqlRet == SQL_SUCCESS )
            {
               sSqlRet = SQLFetch( gStmt );
               if( sSqlRet != SQL_SUCCESS )
               {
                  break;
               }
               else
               {
                  if( sState != 1 )
                  {
                     struct tm sTm1;
                     // convert string time value to tm structure
                     ( void )time_by_str( sTimeStr, &sTm1 );
                     ( void )time(&sT1 );
                     sT2 = mktime(&sTm1 );
                     int sInterval = ( sT1-sT2 )/60;
#ifdef _DEBUG
                     printf( "[%s %d] %s %s %d\n",
                            __FILE__,
                            __LINE__,
                            sCounsellorName,
                            sTimeStr,
                            sInterval );
#endif
                  
                     // time compare
                     if( aReadyTimeVal< sInterval )
                     {
                        snprintf( aChannel[sCount++],
                                  64,
                                  "%s",
                                  sCounsellorName  );
                     }
                  }
                  else
                  {
                     snprintf( aChannel[sCount++],
                               64,
                               "%s",
                               sCounsellorName  );
                  }
               }
               sSqlRet = SQLBindCol( gStmt,
                                     1,
                                     SQL_C_CHAR,
                                     &sCounsellorName,
                                     64,
                                     NULL  );
               if( sSqlRet == SQL_SUCCESS )
               {
                  sSqlRet = SQLBindCol( gStmt,
                                        2,
                                        SQL_C_CHAR,
                                        &sTimeStr,
                                        24,
                                        NULL  );
                  if( sSqlRet != SQL_SUCCESS )
                  {
                     goto close_sql;
                  }
                  else
                  {
                     sSqlRet = SQLBindCol( gStmt,
                                     3,
                                     SQL_C_SSHORT,
                                     &sState,
                                     24,
                                     NULL  );
                  }
               }
               else
               {
                  goto close_sql;
               }
            }
         }
         *aCount = sCount;
      }
close_sql:
      sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
      if( sSqlRet == SQL_SUCCESS )
      {
         return 0;
      }
   }
#else /* HAVE_SQL_H */
   *aCount = 0;
#endif /* HAVE_SQL_H */
   return 1;
}

/*******************************************************
 * 
 * Get Customer's full channel info by Conference Name.
 *
 * @param [in] aConfName
 * @param [in] aOutChanName
 * @param [out] aChannelBufPtr
 * @param [in] aChannelBufLen
 *
 * @return result code
 *
 *******************************************************/
int online_sql_get_customer( char *aConfName,
                            char *aOutChanName,
                            char *aChannelBufPtr,
                            int  aChannelBufLen )
{
#ifdef HAVE_SQL_H
   SQLRETURN sSqlRet;
   char      sQuery[256];

   if( aConfName == NULL )
   {
      goto err_get_customer;
   }
   
   /* Allocate a statement handle */
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                256,
                SEL_CHA_ONLINE_WHERE5,
                aConfName,
                aOutChanName );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            sSqlRet = SQLBindCol( gStmt,
                                  1,
                                  SQL_C_CHAR,
                                  &aChannelBufPtr,
                                  aChannelBufLen,
                                  NULL  );
            if( sSqlRet == SQL_SUCCESS )
            {
               sSqlRet = SQLFetch( gStmt );
               if( sSqlRet == SQL_SUCCESS )
               {
                  sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
                  if( sSqlRet != SQL_SUCCESS )
                  {
                     goto err_get_customer;
                  }
               }
            }
         }
      }
      SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
   }
   else
   {
      return 1;
   }
#else /* have_sql_h */
   goto err_get_customer;
#endif /* HAVE_SQL_H */
   
err_get_customer:
   
   return 0;
}

int online_sql_get_channel_by_callid( char *aMemberNumPtr,
                                      char *aChannelBufPtr,
                                      int  *aChannelBufLen )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet = SQL_SUCCESS;
   int       sRetVal = 0;
   char      sQuery[256];
   
   if( aMemberNumPtr != NULL )
   {
      if( aChannelBufPtr != NULL )
      {
         sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            snprintf( sQuery,
                      256,
                      SEL_CHA_ONLINE_WHERE6,
                      aMemberNumPtr );

            sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
            if( sSqlRet == SQL_SUCCESS )
            {
               sSqlRet = SQLExecute( gStmt  );
               if( sSqlRet == SQL_SUCCESS )
               {
                  sSqlRet = SQLBindCol( gStmt,
                                        1,
                                        SQL_C_CHAR,
                                        aChannelBufPtr,
                                        *aChannelBufLen,
                                        NULL );
                  if( sSqlRet == SQL_SUCCESS )
                  {
                     sSqlRet = SQLFetch( gStmt );
                     if( sSqlRet == SQL_SUCCESS )
                     {
                        *aChannelBufLen = strlen( aChannelBufPtr  );
                        sRetVal = 1;
                     }
                     else
                     { 
                        *aChannelBufLen = 0;
                        /* not found */
                        /* do nothing */
                     }
                  }
                  else
                  {
                     *aChannelBufLen = 0;
                     /* fail to SQLBindCol*/
                     /* do nothing */
                  }
               }
            }
            
            sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
            if( sSqlRet != SQL_SUCCESS )
            {
               /* do nothing */
            }
            else
            {
               /* do nothing */
            }
         }
         else
         {
            /* fail to alloc handle */
            /* do nothing */
         }
      }
      else
      {
         /* do nothing */
      }
   }
   else
   {
      /* do nothing */
   }
   return sRetVal;
#else /* HAVE_SQL_H */
   *aChannelBufLen = 0;
   return 0;
#endif /* HAVE_SQL_H */
}

int online_sql_get_channellist( char * aConfName,
                                char   aChannel[][64],
                                int  * aCount,
                                int    aAll )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   int       sCount=0;
   char      sQuery[256];

   *aCount = 0;
   
   if( ( aConfName == NULL ) && ( aAll == 0 ) )
   {
      return 0;
   }
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt );
   if( sSqlRet == SQL_SUCCESS )
   {
      if( aAll != 0 )
      {
         if( aConfName )
         {
            snprintf( sQuery,
                      256,
                      SEL_CHA_ONLINE_WHERE1,
                      aConfName  );
         }
         else
         {
            snprintf( sQuery,
                      256,
                      SEL_CHA_ONLINE_ALL );
         }
      }
      else
      {
         if( aConfName )
         {
            snprintf( sQuery,
                      256,
                      SEL_CHA_ONLINE_WHERE2,
                      aConfName );
         }
         else
         {
            snprintf( sQuery,
                      256,
                      SEL_CHA_ONLINE_WHERE3 );
         }
      }
      
      sSqlRet = SQLPrepare( gStmt, (SQLCHAR *)&sQuery, SQL_NTS );
      
      if( sSqlRet == SQL_SUCCESS )
      {
         if( SQLExecute( gStmt ) == SQL_SUCCESS )
         {
            sSqlRet = SQLBindCol( gStmt,
                                  1,
                                  SQL_C_CHAR,
                                  &aChannel[sCount++],
                                  64,
                                  NULL );
            while( sSqlRet == SQL_SUCCESS )
            {
               sSqlRet = SQLFetch( gStmt );
               if( sSqlRet != SQL_SUCCESS )
               {
                  sCount--;
                  break;
               }
               
               sSqlRet = SQLBindCol( gStmt,
                                     1,
                                     SQL_C_CHAR,
                                     &aChannel[sCount++],
                                     64,
                                     NULL );
            }
         }
         *aCount = sCount;
      }
      sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
      if( sSqlRet != SQL_SUCCESS )
      {
         return 0;
      }
   }
   return 1;
#else /* HAVE_SQL_H */
   *aCount = 0;
   return 0;
#endif /* HAVE_SQL_H */
}

int online_sql_get( ONLINE * aOnline )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   int       sCount=0;
   char      sQuery[256];
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      
      snprintf( sQuery,
                256,
                "%s",
                SELECT_ONLINE_ALL  );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLBindCol( gStmt,
                               1,
                               SQL_C_SLONG,
                               &aOnline->mId,
                               sizeof( aOnline->mId ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }

         sSqlRet= SQLBindCol( gStmt,
                              2,
                              SQL_C_SLONG,
                              &aOnline->mMemberId,
                              sizeof( aOnline->mMemberId ),
                              NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               3,
                               SQL_C_CHAR,
                               aOnline->mConference,
                               sizeof( aOnline->mConference ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               5,
                               SQL_C_SLONG,
                               &aOnline->mTalking,
                               sizeof( aOnline->mTalking ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               6,
                               SQL_C_SLONG,
                               &aOnline->mMuted,
                               sizeof( aOnline->mMuted ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               7,
                               SQL_C_CHAR,
                               aOnline->mNumber,
                               sizeof( aOnline->mNumber ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               8,
                               SQL_C_CHAR,
                               aOnline->mName,
                               sizeof( aOnline->mName ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               9,
                               SQL_C_SLONG,
                               &aOnline->mAdmin,
                               sizeof( aOnline->mAdmin ),
                               NULL  );
         if( sSqlRet != SQL_SUCCESS )
         {
            goto error;
         }
         
         sSqlRet = SQLBindCol( gStmt,
                               10,
                               SQL_C_CHAR,
                               &aOnline->mUniqueId,
                               sizeof( aOnline->mUniqueId ),
                               NULL  );
         if( sSqlRet == SQL_SUCCESS )
         {
            sSqlRet = SQLExecute( gStmt );
            if( sSqlRet == SQL_SUCCESS )
            {
               while( sSqlRet==SQL_SUCCESS )
               {
                  sSqlRet = SQLFetch( gStmt );
                  if( sSqlRet == SQL_NO_DATA )
                     break;
                  sCount++;
               }
            }
         }
         goto normal;
normal:
         if( SQLFreeHandle( SQL_HANDLE_STMT, gStmt ) != SQL_SUCCESS )
         {
             return 0;
         }
      }
   }
   return 1;
error:
   printf( "[%s] SQLBindCol error.\n",__FILE__ );
   ( void )SQLFreeStmt( gStmt, SQL_DROP );
   return 0;
#else /* HAVE_SQL_H */
   return 0;
#endif /* HAVE_SQL_H */
}

int online_sql_set_confjoin( ONLINE * aOnline )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[512];
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                512,
                "%s '%d','%s','%s','%s','%s','%s','1','1','' );",
                SET_ONLINE_JOIN,
                aOnline->mMemberId,
                aOnline->mConference,
                aOnline->mChannel,
                aOnline->mUniqueId,
                aOnline->mNumber,
                aOnline->mName
               );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
             /* do nothing */
         }
         else
         {
            printf( "[%s] SQLExecute error.\n",__FILE__ );
         }
      }
      else
      {
          /* do nothing */
      }
      
      sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
      if( sSqlRet != SQL_SUCCESS )
      {
         return 0;
      }
      return 1;
   }
   printf( "[%s] SQLAllocHandle error.\n",__FILE__ );
#endif /* HAVE_SQL_H */
   return 0;
}

int online_sql_get_count( char * aConferenceName )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[512] = {0,};
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                512,
                SEL_CNT_ONLINE_WHERE1,
                aConferenceName );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            /* do nothing */
         }
         else
         {
            printf( "[%s] SQLExecute error.\n",__FILE__ );
         }
      }
      if( SQLFreeHandle( SQL_HANDLE_STMT, gStmt ) != SQL_SUCCESS )
      {
         return 0;
      }
      return 1;
   }
   printf( "[%s] SQLAllocHandle error.\n",__FILE__ );
#endif /* HAVE_SQL_H */
   return 0;
}

int online_sql_set_confstate( char * aState, char * aChannel )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[512] = {0,};
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                512,
                "%s talking='%d' where channel='%s' limit 1;",
                SET_ONLINE_STATE,
                (!strcmp( aState,"speaking")?1:0 ),
                aChannel );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS  );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            /* do nothing */
         }
         else
         {
            printf( "[%s] SQLExecute error.\n",__FILE__ );
         }
      }
      
      sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt  );
      if( sSqlRet != SQL_SUCCESS )
      {
         return 0;
      }
      return 1;
   }
   printf( "[%s] SQLAllocHandle error.\n",__FILE__ );
#endif /* HAVE_SQL_H */
   return 0;
}

int online_sql_set_channelstate( char * aChannel, int aState )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[512] = {0,};
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                512,
                UPD_ONLINE_WHERE2,
                aState,
                aChannel  );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            /* do nothing */
         }
         else
         {
            printf( "[%s] SQLExecute error.\n", __FILE__ );
         }
      }
      
      sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt  );
      if( sSqlRet != SQL_SUCCESS )
      {
         return 0;
      }
      else
      {
         return 1;
      }
   }
#endif /* HAVE_SQL_H */
   return 0;
}

/****************************************************
 * delete user information in user's leaving.
 *
 * @param [in]  aConference
 * @param [in]  aChannel
 * @param [in]  aMember 
 *
 * @return return code
 *
 ****************************************************/

int online_sql_del_conf( char * aConference,
                         char * aChannel,
                         int    aMember )
{
#ifdef HAVE_SQL_H
   /* Allocate a statement handle */
   SQLRETURN sSqlRet;
   char      sQuery[512] = {0,};
   
   sSqlRet = SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt  );
   if( sSqlRet == SQL_SUCCESS )
   {
      snprintf( sQuery,
                512,
                "%s'%s' and member_id='%d' and "\
                "channel = '%s' limit 1;",
                DEL_ONLINE_LEAVE,
                aConference,
                aMember,
                aChannel );
      
      sSqlRet = SQLPrepare( gStmt, ( SQLCHAR*)&sQuery, SQL_NTS );
      if( sSqlRet == SQL_SUCCESS )
      {
         sSqlRet = SQLExecute( gStmt );
         if( sSqlRet == SQL_SUCCESS )
         {
            /*do nothing */
         }
         else
         {
            printf( "[%s] SQLExecute error.\n",__FILE__ );
         }
      }
       
      sSqlRet = SQLFreeHandle( SQL_HANDLE_STMT, gStmt );
      if( sSqlRet != SQL_SUCCESS )
      {
         return 0;
      }
      else
      {
         /* do nothing */
      }
      return 1;
   }
   printf( "[%s] SQLAllocHandle error.\n",__FILE__ );
#endif /* HAVE_SQL_H */
   return 0;
}

#ifdef _TEST_DB_MAIN
int main( int aArgc, char **aArgv )
{
#ifdef HAVE_SQL_H
   ONLINE sOnline;
   char   sChannel[10][64];
   int    sOption = 0;
   
   int    sCnt      = 0;
   int    sWaitTime = 3;
   int    sCount    = 0;

   if( aArgc == 1 )
   {
      printf( "Usage : %s [option]\n", aArgv[0] );
      return 0;
   }
   else
   {
      /* do nothing */
   }
   
   sOption = atoi( aArgv[1] );
   
   if( !online_sql_connect() )
   {
      printf( "Main : sql_connect error!\n" );
   }
   
   if( sOption == 0 )
   {  // select all
      online_sql_get( &sOnline );
   }
   else if( sOption == 1 )
   {  // update confjoin
      sOnline.mMemberId = 20001;
      sprintf( sOnline.mConference, "%s", "2001" );
      sprintf( sOnline.mChannel,    "%s", "SIP/2001" );
      sprintf( sOnline.mUniqueId,   "%s", "1222.3" );
      sprintf( sOnline.mNumber,     "%s", "2001" );
      sprintf( sOnline.mName,       "%s", "2001" );
      
      online_sql_set_confjoin( &sOnline );
   }
   else if( sOption == 2 )
   {
      // update confleave
      online_sql_del_conf( (char *)"2001", ( char*)"SkyCom", 2001 );
   }
   else if( sOption == 3 )
   {  // update confstate
      online_sql_set_confstate( (char *)"speaking",( char*)"SIP/2001");
   }
   else if( sOption == 4 )
   {  // delete all confleave
   }
   else if( sOption == 5 )
   {  // delete selection
   }
   else if( sOption == 6 )
   {
      sCount = 10;
      online_sql_get_channellist( (char *)"6003", sChannel, &sCount, 0 );
      if( sCount > 0 )
      {
         for( sCnt = 0 ; sCnt < sCount ; sCnt++ )
         {
            printf( "[%s %d] %s\n", __FILE__,__LINE__, sChannel[sCnt] );
         }
      }
      else
      {
         printf( "[%s %d] not found..\n", __FILE__, __LINE__ );
      }
   }
   else if( sOption == 7 )
   {
      sCount = 10;
      if( aArgc == 3 )
      {
          sWaitTime = atoi( aArgv[2] );
      }
      online_sql_get_counsellorlist( 0,
                                     sWaitTime,
                                     sChannel,
                                     &sCount );
      if( sCount > 0 )
      {
         for( sCnt = 0 ; sCnt < sCount; sCnt++ )
         {
            printf( "[%s %d] %s\n", __FILE__,__LINE__, sChannel[sCnt] );
         }
      }
      else
      {
         printf( "[%s %d] not found..\n", __FILE__, __LINE__ );
      }
   }
   else if( sOption == 8 )
   {
      online_sql_set_lasttalk( aArgv[2], aArgv[3] );
   }
   else if( sOption == 9 )
   {
      if( aArgc !=  4 )
      {
         printf( "%s [option = 9] [channel name] [state, 0/1]\n",
                 aArgv[0] );
      }
      else
      {
         online_sql_set_channelstate( aArgv[2], atoi( aArgv[3]) );
      }
   }
   online_sql_disconnect( );
#else /* HAVE_SQL_H */
   printf( "%s don't support to ODBC.\n", aArgv[0] );
#endif /* HAVE_SQL_H */
}
#endif // _TEST_DB_MAIN

//////////////////////////////////////////////////////////////////
// end of file
